package com.bilibili.sys.dao.impl;

import com.bilibili.sys.bean.SysUser;
import com.bilibili.sys.dao.IUserDao;
import com.bilibili.sys.utils.MyDBUtils;
import com.bilibili.sys.utils.PageUtils;
import com.bilibili.sys.utils.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author alpha
 * @program: BookSystem
 * @description: 系统用户Dao的实现类
 * @since 2024-03-24 22:28
 **/

public class UserDaoImpl implements IUserDao {
    @Override
    public List<SysUser> list(SysUser user) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select * from sys_user where 1 = 1";
        if(user.getRoleId() != null && user.getRoleId() > 0){
            //添加查询的条件
            sql = sql + "and role_id =" + user.getRoleId();
        }
        try {
            //BeanListHandler会自动帮助我们完成字段和属性的映射，前提是属性和字段完全一致
            //List<SysUser> list = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class));
            List<SysUser> list = queryRunner.query(sql, new ResultSetHandler<List<SysUser>>() {
                @Override
                public List<SysUser> handle(ResultSet resultSet) throws SQLException {
                    List<SysUser> list = new ArrayList<>();
                    while (resultSet.next()){
                        //每循环一次 user存储一条数据
                        SysUser user = new SysUser();
                        user.setId(resultSet.getInt("id"));
                        user.setUsername(resultSet.getString("username"));
                        user.setPassword(resultSet.getString("password"));
                        user.setNickname(resultSet.getString("nickname"));
                        user.setRoleId(resultSet.getInt("role_id"));
                        user.setRoleName(resultSet.getString("rolename"));
                        user.setImg(resultSet.getString("img"));
                        user.setCreateTime(resultSet.getDate("create_time"));
                        list.add(user);
                    }
                    return list;
                }
            });
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 分页查询的操作
 * @param pageUtils
     * @return java.util.List<com.bilibili.sys.bean.SysUser>
     * @author alpha
     * @since 2024/03/27 16:03
     */
    @Override
    public List<SysUser> listPage(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select * from sys_user limit ?,?";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            //需要带条件查询
            sql = "select * from sys_user  where username like '%"+pageUtils.getKey()+"%' or nickname  like '%"+pageUtils.getKey()+"%' limit ?,?";
        }
        //计算分页开始的位置（前端传递过来从1开始）
        int startNo = pageUtils.getStart();
        try {
            //BeanListHandler会自动帮助我们完成字段和属性的映射，前提是属性和字段完全一致
            //List<SysUser> list = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class));
            List<SysUser> list = queryRunner.query(sql, new ResultSetHandler<List<SysUser>>() {
                @Override
                public List<SysUser> handle(ResultSet resultSet) throws SQLException {
                    List<SysUser> list = new ArrayList<>();
                    while (resultSet.next()){
                        SysUser user = new SysUser();
                        user.setId(resultSet.getInt("id"));
                        user.setUsername(resultSet.getString("username"));
                        user.setPassword(resultSet.getString("password"));
                        user.setNickname(resultSet.getString("nickname"));
                        user.setRoleId(resultSet.getInt("role_id"));
                        user.setRoleName(resultSet.getString("rolename"));
                        user.setImg(resultSet.getString("img"));
                        user.setCreateTime(resultSet.getDate("create_time"));
                        list.add(user);
                    }
                    return list;
                }
            },startNo,pageUtils.getPageSize());//从pageUtils位置开始，取getPageSize条记录
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int save(SysUser user) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "insert into sys_user(username,password,nickname,img)values(?,?,?,?)";
        try {
            return queryRunner.update(sql,user.getUsername(),user.getPassword(),user.getNickname(),user.getImg());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public SysUser findById(int id) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select * from sys_user where id = ?";
        try {
            //BeanListHandler会自动帮助我们完成字段和属性的映射，前提是属性和字段完全一致
            //List<SysUser> list = queryRunner.query(sql, new BeanListHandler<SysUser>(SysUser.class));
            SysUser user = queryRunner.query(sql, new ResultSetHandler<SysUser>() {
                @Override
                public SysUser handle(ResultSet resultSet) throws SQLException {
                        if (resultSet.next()){
                            SysUser user = new SysUser();
                            user.setId(resultSet.getInt("id"));
                            user.setUsername(resultSet.getString("username"));
                            user.setPassword(resultSet.getString("password"));
                            user.setNickname(resultSet.getString("nickname"));
                            user.setRoleId(resultSet.getInt("role_id"));
                            user.setRoleName(resultSet.getString("rolename"));
                            user.setImg(resultSet.getString("img"));
                            user.setCreateTime(resultSet.getDate("create_time"));
                            return user;
                    }
                    return null;
                }
            },id);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateById(SysUser user) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "update sys_user set username=?,password=?,nickname=?,img=? where id=?";
        try {
            return queryRunner.update(sql,user.getUsername(),user.getPassword(),user.getNickname(),user.getImg(),user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public int deleteById(int id) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "delete from sys_user where id = ?";
        try {
            return queryRunner.update(sql,id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public int count(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select count(*) from sys_user";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            sql = "select count(*) from sys_user where username like '%" + pageUtils.getKey() + "%' or nickname  like '%" + pageUtils.getKey() + "%' ";
        }
        try {
            return queryRunner.query(sql, new ResultSetHandler<Integer>() {
                @Override
                public Integer handle(ResultSet resultSet) throws SQLException {
                    resultSet.next();
                    return resultSet.getInt(1);
                }
            });
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public static void main(String[] args) {
        //UserDaoImpl userDao = new UserDaoImpl();
        IUserDao dao =new UserDaoImpl();
        /*List<SysUser> list = userDao.listPage(null,2,4);
        for (SysUser sysUser : list) {
            System.out.println(sysUser);
        }*/
    }
}
